package ws.performancetest.tests.stdstudent;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import ws.utils.jdbc.DBConnection;

/**
 *
 * @author Wilson Santos, 36228 <wilson.santos@ua.pt>
 */
public class StudentTable {

  /** Database connection. */
  private static Connection con;
  /** Name of the SQL table. */
  private static String tableName = "Std_Student";

  static {
    try {
      /* initialize connection. */
      con = DBConnection.getDBCon();
    } catch (SQLException ex) {
      exitOnSQLException(ex);
    }
  }

  public static void main(String[] args) {
    int[] rowSet = {150, 250, 500};
    try {
      for (int rowAmount : rowSet) {
        /* remove all lines from table. */
        removeStudents();

        /* insert lines in table. */
        createStudents(rowAmount);

        /* Create statement forward only and updatable. */
        Statement stmt = con.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_UPDATABLE);

        /* get all students. */
        ResultSet rs = getStudents(stmt);

        /* change one of the table fields. */
        int i = 1;
        while(rs.next()) {
          System.out.println("row=" + rs.getRow());
          rs.updateInt("Std_Id", rowAmount + i);
          rs.updateRow();
          ++i;
        }
      }
      con.close();
      System.out.println("Task completed!");
    } catch (SQLException ex) {
      exitOnSQLException(ex);
    }
  }

  /**
   * Print SQLException message and exit with SQL error code.
   *
   * @param ex SQLException that has been thrown.
   */
  private static void exitOnSQLException(SQLException ex) {
    System.err.println(ex.getClass().getSimpleName() + ": " + ex.getMessage());
    ex.printStackTrace();
    System.exit(ex.getErrorCode());
  }

  /**
   * Insert {@code amount} students on the table.
   * 
   * @param amount student amount.
   */
  private static void createStudents(int amount) {
    try {
      con.setAutoCommit(false);
      Statement stmt = con.createStatement();
      for (int i = 0; i < amount; ++i) {
        String sql = "INSERT INTO " + tableName + " "
                + "(Std_id, Std_firstName, Std_familyName, Std_RegDate, Std_applGrade, StdCrs_id, StdNat_id, Std_birthDate, Std_eMail, Std_mPhone, Std_VAT) "
                + "VALUES(" + i + ", 'fn', 'ln', '9999-12-31', 0.0, 0, 0, '9999-12-31', 'mail', 'ph', 'vat')";
        stmt.addBatch(sql);
      }
      stmt.executeBatch();
      stmt.close();
      con.commit();
      con.setAutoCommit(true);
    } catch (SQLException ex) {
      try {
        con.rollback();
      } catch (SQLException ex1) {
        exitOnSQLException(ex1);
      }
      exitOnSQLException(ex);
    }
  }

  /**
   * Remove all students from the table.
   */
  private static void removeStudents() {
    try {
      String sql = "DELETE FROM " + tableName;
      Statement stmt = con.createStatement();
      stmt.executeUpdate(sql);
      stmt.close();
    } catch (SQLException ex) {
      exitOnSQLException(ex);
    }
  }

  /**
   * Select all students from the table.
   * 
   * @return {@link ResultSet} with all students from the table.
   */
  private static ResultSet getStudents(Statement stmt) {
    ResultSet rs = null;
    try {
      String sql = "SELECT * FROM " + tableName;
      rs = stmt.executeQuery(sql);
    } catch (SQLException ex) {
      exitOnSQLException(ex);
    }
    return rs;
  }
}